Index...
Centrallix Documentation
|
6.5 GROUP BY Clause
Grouping and Aggregates
Centrallix supports grouping / aggregating data using a GROUP BY clause and aggregate functions. If aggregate functions are used without a GROUP BY clause, the entire query is considered a single group, resulting in a one-row result set.
Centrallix differs a little bit from the standard SQL semantics in its handling of the GROUP BY clause. It is not necessary to list every non-aggregate field in the GROUP BY clause, only enough to specify the grouping that is desired. This permits, for example, doing a GROUP BY in a query containing a SELECT *. This is similar to MySQL semantics, and different from those of, for instance, Sybase and MS SQL Server.
Examples:
SELECT count(1), sum(:d:size) from /mydirectory d
SELECT sum(:d:name + ', ') from /mydirectory d
SELECT :d:owner, sum(:d:size) from /mydirectory d GROUP BY :d:owner
Aggregate Functions
Centrallix currently supports the aggregate functions sum(), count(), avg(), min(), and max(). Also supported are first(), last(), and nth().
A column name or a constant value must be given to count(). Using "count(1)" is semantically the same as "count(*)" would be. When using a field name for count(), the row is not counted if the field is NULL.
The sum() function also has an interesting behavior: Centrallix's plus "+" operator allows string concatenation, so sum()ing a string field will return the concatenation of those strings.
With the exception of count(), these functions return NULL if no records were matched. count() in that case returns 0 (zero).
Nesting Aggregates
Centrallix SQL allows aggregate functions to be nested when a GROUP BY clause is used. In this case, the entire result set is summarized into a single row, but grouping is done as an intermediate step in computing the final result set.
For example, to find the average number of objects in each group, you might use a query like this one:
SELECT avg(count(1))
FROM /datasource/mytable/rows t
GROUP BY :t:product_category
Comments...
(none yet)
Add a Comment...
|
